#pragma once
#include <iostream>
#include <mysql/mysql.h>
#include "jsoncpp-1.9.5/include/json/json.h"
#include <sstream>
#include <iomanip>
#include <chrono>
#include <openssl/sha.h>
#include <queue>
#include <mysql/mysql_com.h>
#include <mutex>
#include <condition_variable>
#include <stdexcept>
#include <atomic>
#include <thread>
#include <unordered_set>

class DBConnectionPool
{
public:
    static DBConnectionPool *getInstance()
    {
        static DBConnectionPool instance;
        return &instance;
    }

    void initialize(const std::string &host, const std::string &user,
                    const std::string &password, const std::string &db,
                    unsigned int port, unsigned int poolSize = 10)
    {
        std::lock_guard<std::mutex> lock(mutex_);
        host_ = host;
        user_ = user;
        password_ = password;
        db_ = db;
        port_ = port;
        poolSize_ = poolSize;
        running_ = true;

        for (unsigned int i = 0; i < poolSize_; ++i)
        {
            std::cout<< "i: "<< i<<std::endl;
            MYSQL *conn = createConnectionWithRetry();

            if (!conn) {
                std::cerr << "[ConnectionPool] Failed to create connection #" << i << std::endl;
            }else
            {
                std::cout <<"success"<<std::endl;
            }
            connections_.push(conn);
            allConnections_.push_back(conn);
        }
        std::cout <<"connections_.size"<<connections_.size()<<std::endl;
        

        monitorThread_ = std::thread(&DBConnectionPool::monitorConnections, this);
    }

    MYSQL *getConnection()
    {
        std::cout <<"connections_.size"<<connections_.size()<<std::endl;
        std::unique_lock<std::mutex> lock(mutex_);
        std::cout << "getConnection()"<<std::endl;
        std::cout << "connections_.empty()"<<connections_.empty()<<std::endl;
        while (connections_.empty())
            cond_.wait(lock);

        MYSQL *conn = connections_.front();
        connections_.pop();
        busy_.insert(conn);

        if (mysql_ping(conn))
        {
            std::cerr << "[ConnectionPool] Bad connection, re-establishing..." << std::endl;
            conn = recreateConnection(conn);
        }

        return conn;
    }

    void releaseConnection(MYSQL *conn)
    {
        std::lock_guard<std::mutex> lock(mutex_);
        busy_.erase(conn);
        connections_.push(conn);
        cond_.notify_one();
    }

    ~DBConnectionPool()
    {
        running_ = false;
        if (monitorThread_.joinable())
            monitorThread_.join();

        std::lock_guard<std::mutex> lock(mutex_);
        for (auto conn : allConnections_)
            mysql_close(conn);
    }

private:
    DBConnectionPool() : running_(false) {}

  
    MYSQL *createConnectionWithRetry(int maxRetry = 5, int delayMs = 1000) {
        int retry = 0;
        while (retry < maxRetry) {  

            std::cout <<"createConnectionWithRetry()"<<std::endl;
            MYSQL *conn = mysql_init(nullptr);
            if (!conn) {
                std::cerr << "mysql_init() failed" << std::endl;
                return nullptr; 
            }

            if (mysql_real_connect(conn, host_.c_str(), user_.c_str(), 
                                password_.c_str(), db_.c_str(), 
                                port_, nullptr, 0)) {
                std::cout << "Connection established" << std::endl;
                return conn;
            } else {
                std::cerr << "Connection failed (attempt " << retry+1 
                        << "): " << mysql_error(conn) << std::endl;
                mysql_close(conn);
                std::this_thread::sleep_for(std::chrono::milliseconds(delayMs));
                retry++;
            }
        }
        return nullptr; // 所有重试失败后返回空指针
    }
    MYSQL *recreateConnection(MYSQL *oldConn)
    {
        mysql_close(oldConn);
        MYSQL *newConn = createConnectionWithRetry();
        std::replace(allConnections_.begin(), allConnections_.end(), oldConn, newConn);
        return newConn;
    }

    void monitorConnections()
    {
        while (running_)
        {
            std::this_thread::sleep_for(std::chrono::seconds(10));

            std::lock_guard<std::mutex> lock(mutex_);
            for (auto &conn : allConnections_)
            {
                if (busy_.count(conn))
                    continue; // 跳过使用中的连接

                if (mysql_ping(conn))
                {
                    std::cerr << "[Monitor] Detected dead connection, recovering..." << std::endl;
                    MYSQL *newConn = recreateConnection(conn);
                    conn = newConn;
                }
            }
        }
    }

    std::queue<MYSQL *> connections_;
    std::vector<MYSQL *> allConnections_;
    std::unordered_set<MYSQL *> busy_;

    std::string host_, user_, password_, db_;
    unsigned int port_;
    unsigned int poolSize_;

    std::mutex mutex_;
    std::condition_variable cond_;

    std::atomic<bool> running_;
    std::thread monitorThread_;
};

class CardDeck
{
public:
    
    int cardDeckID;   
    int userAutoID;   
    int cardID;       
    int cardIDInDeck; 
    int target;       

    CardDeck(int cDeckID = -1, int uAutoID = -1, int cID = -1, int cIDInDeck = -1, int tar = -1)
        : cardDeckID(cDeckID), userAutoID(uAutoID), cardID(cID), cardIDInDeck(cIDInDeck), target(tar) {}

    // 用于从 JSON 中填充数据
    void fromJson(const Json::Value &json)
    {
        // 检查 JSON 中的字段是否存在，并赋值
        cardDeckID = json.isMember("cardDeckID") ? json["cardDeckID"].asInt() : -1;
        userAutoID = json.isMember("userAutoID") ? json["userAutoID"].asInt() : -1;
        cardID = json.isMember("cardID") ? json["cardID"].asInt() : -1;
        cardIDInDeck = json.isMember("cardIDInDeck") ? json["cardIDInDeck"].asInt() : -1;
        target = json.isMember("target") ? json["target"].asInt() : -1;
    }

    Json::Value toJson() const
    {
        Json::Value json;
        json["cardDeckID"] = cardDeckID;
        json["userAutoID"] = userAutoID;
        json["cardID"] = cardID;
        json["cardIDInDeck"] = cardIDInDeck;
        json["target"] = target;
        return json;
    }
};

class SaveDeckData
{
public:
    std::string userID;
    std::string competitionRecord;
    std::string userName;
    std::string uploadTime;
    int totalSession;
    int winSession;
    int level;
    std::string fgName;
    std::string extra;
    std::vector<CardDeck> saveCardDatas; 

    void fromJson(const Json::Value &json)
    {

        if (json.isMember("userID"))
            userID = json["userID"].asString();
        if (json.isMember("competitionRecord"))
            competitionRecord = json["competitionRecord"].asString();
        if (json.isMember("userName"))
            userName = json["userName"].asString();
        if (json.isMember("uploadTime"))
            uploadTime = json["uploadTime"].asString();
        if (json.isMember("totalSession"))
            totalSession = json["totalSession"].asInt();
        if (json.isMember("winSession"))
            winSession = json["winSession"].asInt();
        if (json.isMember("level"))
            level = json["level"].asInt();
        if (json.isMember("fgName"))
            fgName = json["fgName"].asString();
        if (json.isMember("extra"))
            extra = json["extra"].asString();

        if (json.isMember("saveCardDatas"))
        {
            const Json::Value &cards = json["saveCardDatas"];
            for (int i = 0; i < cards.size(); ++i)
            {
                CardDeck card;                 
                card.fromJson(cards[i]);       
                saveCardDatas.push_back(card); 
            }
        }
    }


    Json::Value toJson() const
    {
        Json::Value json;
        json["userID"] = userID;
        json["competitionRecord"] = competitionRecord;
        json["userName"] = userName;
        json["uploadTime"] = uploadTime;
        json["totalSession"] = totalSession;
        json["winSession"] = winSession;
        json["level"] = level;
        json["fgName"] = fgName;
        json["extra"] = extra;

        // 将卡组数据转换为 JSON
        Json::Value cardsJson;
        for (const auto &card : saveCardDatas)
        {
            cardsJson.append(card.toJson()); // 将每个卡牌数据转换为 JSON 格式并添加到数组中
        }
        json["saveCardDatas"] = cardsJson;

        return json;
    }
};
// 主任务类
class Task
{
public:
    static Task *getInstance()
    {
        static Task instance;
        return &instance;
    }

    std::string GetUserID(const std::string &clientIP)
    {
        std::string userID = GenerateUserID(clientIP);

        MYSQL *conn = DBConnectionPool::getInstance()->getConnection();

        std::string query = "INSERT INTO users (userID) VALUES ('" + userID + "')";

        if (mysql_query(conn, query.c_str()))
        {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            return "mysql_query failed: " + std::string(mysql_error(conn));
        }

        DBConnectionPool::getInstance()->releaseConnection(conn);

        return userID;
    }

    void UploadDeckArchive(const Json::Value &input_json, Json::Value *output_json) 
    {
        SaveDeckData deckData;
        deckData.fromJson(input_json);



        MYSQL *conn = DBConnectionPool::getInstance()->getConnection();
        if (mysql_query(conn, "START TRANSACTION")) {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Start transaction failed";
            return;
        }

        // 1. 获取用户userAutoID
        std::string query = "SELECT userAutoID FROM users WHERE userID = '" + deckData.userID + "'";
        if (mysql_query(conn, query.c_str())) {
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Query user failed: " + std::string(mysql_error(conn));
            return;
        }

        MYSQL_RES *result = mysql_store_result(conn);
        if (!result) {
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Get user result failed";
            return;
        }

        MYSQL_ROW row = mysql_fetch_row(result);
        if (!row) {
            mysql_free_result(result);
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "User not found";
            return;
        }

        long long userAutoID = std::stoll(row[0]);
        mysql_free_result(result);

       
        query = "INSERT INTO saveCardDatas (userAutoID, userID, userName, uploadTime, totalSession, "
        "winSession, level, fgName, sumUsed, sumWin, extra) VALUES (" +
        std::to_string(userAutoID) + ", '" +
        deckData.userID + "', '" +
        deckData.userName + "', '" +
        deckData.uploadTime + "', " +
        std::to_string(deckData.totalSession) + ", " +
        std::to_string(deckData.winSession) + ", " +
        std::to_string(deckData.level) + ", '" +
        deckData.fgName + "', 0, 0, '" +  // sumUsed=0, sumWin=0
        deckData.extra + "')";

        std::cout<< query <<std::endl;

        if (mysql_query(conn, query.c_str())) {
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Insert card data failed: " + std::string(mysql_error(conn));
            return;
        }

        mysql_query(conn, query.c_str()); 


        for (const auto &card : deckData.saveCardDatas) {
           
            std::cout <<"UploadDeckArchive "<<deckData.uploadTime <<" "<<deckData.userID<<std::endl;
            query = "INSERT INTO cardDeck (userAutoID, cardID, cardIDInDeck, target, uploadTime, userID) VALUES (" +
            std::to_string(userAutoID) + ", " +
            std::to_string(card.cardID) + ", " +
            std::to_string(card.cardIDInDeck) + ", " +
            std::to_string(card.target) + ", '" +  
            deckData.uploadTime + "', '" +             
            deckData.userID + "')";                    

            if (mysql_query(conn, query.c_str())) {
                mysql_query(conn, "ROLLBACK");
                DBConnectionPool::getInstance()->releaseConnection(conn);
                (*output_json)["status"] = "failed";
                (*output_json)["message"] = "Insert card failed: " + std::string(mysql_error(conn));
                return;
            }
        }

        // 提交事务
        if (mysql_query(conn, "COMMIT")) {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Commit failed: " + std::string(mysql_error(conn));
            return;
        }

        DBConnectionPool::getInstance()->releaseConnection(conn);
        (*output_json)["status"] = "success";
    }
    void DownloadOpponentArchive(const Json::Value &input_json, Json::Value *output_json)
    {
        std::string userID = input_json["userID"].asString();
        std::string uploadTime = input_json["uploadTime"].asString();

        std::cout << " DownloadOpponentArchive " <<userID <<" "<<uploadTime<<std::endl;
        
        MYSQL *conn = DBConnectionPool::getInstance()->getConnection();
        Json::Value json;
        json["userID"] = userID;

        std::string query =
            "SELECT userAutoID, userName, uploadTime, totalSession, "
            "winSession, level, fgName, extra FROM saveCardDatas "
            "WHERE userID = '" +
            userID + "' AND uploadTime = '" + uploadTime + "'";

        if (mysql_query(conn, query.c_str()))
        {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = mysql_error(conn);
            return;
        }

        MYSQL_RES *result = mysql_store_result(conn);
        MYSQL_ROW row = mysql_fetch_row(result);
        if (!row)
        {
            mysql_free_result(result);
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Archive not found";
            return;
        }

        long long userAutoID = std::stoll(row[0]);
        json["userName"] = row[1] ? row[1] : "";
        json["uploadTime"] = row[2] ? row[2] : "";
        json["totalSession"] = row[3] ? std::stoi(row[3]) : 0;
        json["winSession"] = row[4] ? std::stoi(row[4]) : 0;
        json["level"] = row[5] ? std::stoi(row[5]) : 0;
        json["fgName"] = row[6] ? row[6] : "";
        json["extra"] = row[7] ? row[7] : "";

        mysql_free_result(result);

     
        
        query = "SELECT cd.cardID, cd.cardIDInDeck, cd.target, cd.userID, cd.uploadTime FROM cardDeck cd "
        "WHERE cd.userID = '" + userID + "' " 
        "AND cd.uploadTime = '" + uploadTime + "' "
        "ORDER BY cd.cardIDInDeck";

        if (mysql_query(conn, query.c_str()))
        {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = mysql_error(conn);
            return;
        }
        std::cout << "====test====" <<std::endl;
        result = mysql_store_result(conn);
        while ((row = mysql_fetch_row(result)))
        {
            std::cout << "not null"<<std::endl; 
            Json::Value card;
            card["cardID"] = row[0] ? std::stoi(row[0]) : 0;
            card["cardIDInDeck"] = row[1] ? std::stoi(row[1]) : 0;
            card["target"] = row[2] ? std::stoi(row[2]) : 0;

            std::cout << "download"<<row[3] << " " <<row[4] << std::endl;
            json["saveCardDatas"].append(card);
        }
        std::cout << "====test2===="<<std::endl; 
        mysql_free_result(result);

         if (json["saveCardDatas"].isNull() || json["saveCardDatas"].empty())
        {
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "saveCardDatas is NULL";
            DBConnectionPool::getInstance()->releaseConnection(conn);
            return;
        }

        DBConnectionPool::getInstance()->releaseConnection(conn);
        json["status"] = "success";
        *output_json = json;
    }
    void DownloadOpponentArchiveWithExtra(const Json::Value &input_json, Json::Value *output_json)
    {
        std::string userID = input_json["userID"].asString();
        std::string uploadTime = input_json["uploadTime"].asString();

        MYSQL *conn = DBConnectionPool::getInstance()->getConnection();
        Json::Value json;
        json["userID"] = userID;


        
        std::string query =
            "SELECT userAutoID, userName, uploadTime, totalSession, "
            "winSession, level, fgName, extra FROM saveCardDatas "
            "WHERE userID = '" + userID + "' AND uploadTime = '" + uploadTime + "'"
            " AND extra = 'NoShuffle'";



        if (mysql_query(conn, query.c_str()))
        {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = mysql_error(conn);
            return;
        }

        MYSQL_RES *result = mysql_store_result(conn);
        MYSQL_ROW row = mysql_fetch_row(result);
        if (!row)
        {
            mysql_free_result(result);
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Archive not found";
            return;
        }

        long long userAutoID = std::stoll(row[0]);
        json["userName"] = row[1] ? row[1] : "";
        json["uploadTime"] = row[2] ? row[2] : "";
        json["totalSession"] = row[3] ? std::stoi(row[3]) : 0;
        json["winSession"] = row[4] ? std::stoi(row[4]) : 0;
        json["level"] = row[5] ? std::stoi(row[5]) : 0;
        json["fgName"] = row[6] ? row[6] : "";
        json["extra"] = row[7] ? row[7] : "";

        mysql_free_result(result);

       

        query = "SELECT cd.cardID, cd.cardIDInDeck, cd.target, cd.userID, cd.uploadTime FROM cardDeck cd "
        "WHERE cd.userID = '" + userID + "' " 
        "AND cd.uploadTime = '" + uploadTime + "' "
        "ORDER BY cd.cardIDInDeck";
        
        
        if (mysql_query(conn, query.c_str()))
        {
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = mysql_error(conn);
            return;
        }

        result = mysql_store_result(conn);
        while ((row = mysql_fetch_row(result)))
        {
            Json::Value card;
            card["cardID"] = row[0] ? std::stoi(row[0]) : 0;
            card["cardIDInDeck"] = row[1] ? std::stoi(row[1]) : 0;
            card["target"] = row[2] ? std::stoi(row[2]) : 0;
            json["saveCardDatas"].append(card);
        }
        mysql_free_result(result);


        if (json["saveCardDatas"].isNull() || json["saveCardDatas"].empty())
        {
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "saveCardDatas is NULL";
            DBConnectionPool::getInstance()->releaseConnection(conn);
            return;
        }

        DBConnectionPool::getInstance()->releaseConnection(conn);
        json["status"] = "success";
        *output_json = json;
    }



    void UploadOpponentData(const Json::Value &input_json, Json::Value *output_json)
    {
        SaveDeckData opponentData;
        opponentData.fromJson(input_json);

        MYSQL *conn = DBConnectionPool::getInstance()->getConnection();
        mysql_query(conn, "START TRANSACTION");

        // 查找对应对手记录
        std::string query = "SELECT sumWin, sumUsed FROM opponent_archive WHERE userID = '" +
                            opponentData.userID + "' AND uploadTime = '" + opponentData.uploadTime + "'";

        if (mysql_query(conn, query.c_str()))
        {
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = mysql_error(conn);
            return;
        }

        MYSQL_RES *result = mysql_store_result(conn);
        MYSQL_ROW row = mysql_fetch_row(result);

        if (!row)
        {
            mysql_free_result(result);
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = "Opponent not found";
            return;
        }

        unsigned long sumWin = std::stoul(row[0]);
        unsigned long sumUsed = std::stoul(row[1]);
        mysql_free_result(result);

        // 构建更新语句
        std::string updateQuery = "UPDATE opponent_archive SET sumUsed = sumUsed + 1";
        
        // 如果需要增加胜场
        if (opponentData.extra == "true") {
            updateQuery += ", sumWin = " + std::to_string(sumWin + 1);
        }
        
        updateQuery += " WHERE userID = '" + opponentData.userID +
                    "' AND uploadTime = '" + opponentData.uploadTime + "'";

        if (mysql_query(conn, updateQuery.c_str()))
        {
            mysql_query(conn, "ROLLBACK");
            DBConnectionPool::getInstance()->releaseConnection(conn);
            (*output_json)["status"] = "failed";
            (*output_json)["message"] = mysql_error(conn);
            return;
        }

        // 提交事务
        mysql_query(conn, "COMMIT");
        DBConnectionPool::getInstance()->releaseConnection(conn);
        (*output_json)["status"] = "success";
    }

private:
    Task()
    {}

    std::string GenerateUserID(const std::string &clientIP)
    {
        std::cout << "GenerateUserID: " << clientIP << std::endl;
        using namespace std::chrono;
        auto now = system_clock::now();
        auto millis = duration_cast<milliseconds>(now.time_since_epoch()).count();

        std::stringstream ss;
        ss << clientIP << "-" << millis;

        unsigned char hash[SHA256_DIGEST_LENGTH];
        SHA256(reinterpret_cast<const unsigned char *>(ss.str().c_str()), ss.str().length(), hash);

        std::stringstream hexStream;
        for (int i = 0; i < SHA256_DIGEST_LENGTH; i++)
        {
            hexStream << std::hex << std::setw(2) << std::setfill('0') << static_cast<int>(hash[i]);
        }
        std::cout << "hexStream.str() " << hexStream.str() << std::endl;

        return hexStream.str();
    }
};
